<?php 

//PAJAK
function kalkulasi_pajak_yearly($karyawan ,  $gaji_dibayar , $status_create  ){
	
	//SET KOLOM pajak 1 
	$total_tunjangan = get_total_pendapatan_sebelum_pph_yearly($karyawan['karyawan_id'] , 3,$status_create);
	$total_tunjangan_premi = get_total_pendapatan_sebelum_pph_yearly($karyawan['karyawan_id'] , 5,$status_create );
	 
		$total_pendapatan = $gaji_dibayar + $total_tunjangan + $total_tunjangan_premi;
	
	$total_iuran= get_total_pendapatan_sebelum_pph_yearly($karyawan['karyawan_id'] , 12,$status_create );
	$biaya_jabatan = get_biaya_jabatan_yearly($total_pendapatan);
	$total_pengurang_pajak = $total_iuran + $biaya_jabatan;
	
	$penghasilan_neto = $total_pendapatan - $total_pengurang_pajak ;
	
	$penhasilan_disetahunkan = 12 * round($penghasilan_neto);	
	//CHECK DATA REFERENSI
	$ptkp_karyawan = get_ptkp_karyawan_yearly($karyawan['ptkp_status'] , $karyawan['ptkp_tanggung'] );
	$pendapatan_disetahunkan =  $penhasilan_disetahunkan - $ptkp_karyawan;
	
	$besar_pajak = tarif_progressif_yearly($pendapatan_disetahunkan);
	
	$perbulan = $besar_pajak / 12;
	$rperbulan = round( $perbulan , -2 ) ;
	$rperbulan = ( $rperbulan < 0 ) ? 0 : $rperbulan ;
	$datas = array(
		'pr_yearly_income_id'	=> my_type_data_int($status_create),
		'karyawan_id'	=> my_type_data_int($karyawan['karyawan_id']),
		'pajak_01'	=> my_type_data_str($gaji_dibayar),
		'pajak_02'	=> my_type_data_str( $rperbulan ),
		'pajak_03'	=> my_type_data_str($total_tunjangan ),
		'pajak_04'	=> my_type_data_str( 0 ),
		'pajak_05'	=> my_type_data_str( $total_tunjangan_premi ),
		
		'pajak_06'	=> my_type_data_str( 0 ),
		'pajak_07'	=> my_type_data_str( $total_pendapatan ),
		
		'pajak_08'	=> my_type_data_str( 0 ),
		'pajak_09'	=> my_type_data_str( $total_pendapatan ),
		'pajak_10'	=> my_type_data_str( $biaya_jabatan ),
		'pajak_11'	=> my_type_data_str( 0 ),
		
		'pajak_12'	=> my_type_data_str( $total_iuran ),
		'pajak_13'	=> my_type_data_str( $total_pengurang_pajak ),
		
		'pajak_14'	=> my_type_data_str( round($penghasilan_neto) ),
		'pajak_15'	=> my_type_data_str( 0 ),
		
		'pajak_16'	=> my_type_data_str( round($penghasilan_neto * 12) ),
		'pajak_17'	=> my_type_data_str( $ptkp_karyawan ),
		
		'pajak_18'	=> my_type_data_str(round( $pendapatan_disetahunkan )),
		'pajak_19'	=> my_type_data_str( round($besar_pajak )),
		
		'pajak_20'	=> my_type_data_str( 0 ),
		'pajak_21'	=> my_type_data_str($rperbulan),
		'pajak_22'	=> my_type_data_str( 0 ),
		'pajak_23'	=> my_type_data_str( $rperbulan ), 
		
	);
	
	
	if($status_create == 0){
		$idt = my_insert_record( 'pr_yearly_income_pajak' , $datas);
	}else {
		$idt = $status_create;
		$idt = my_insert_record( 'pr_yearly_income_pajak' , $datas);
		//my_update_record( 'pr_yearly_income_pajak' , 'pr_yearly_income_id' , $status_create, $datas);
	} 


	$total_tunjangan_baru = $total_tunjangan + $rperbulan; 
	$biaya_jabatan_baru = get_biaya_jabatan_yearly($total_pendapatan + $rperbulan);
	$total_pengurang_pajak_baru = $total_iuran + $biaya_jabatan_baru;
	
	$penghasilan_neto_baru = $total_tunjangan_baru + $gaji_dibayar - $total_pengurang_pajak_baru ;
	$tarif_pajak_baru = ( $penghasilan_neto_baru * 12 ) - $ptkp_karyawan;
	$besar_pajak_baru = tarif_progressif_yearly($tarif_pajak_baru);
	$pajak_baru = round( $besar_pajak_baru /12 , -2 );
	$cdatas = array(  
		'pajak_02'	=> my_type_data_str( $rperbulan ),
		'pajak_07'	=> my_type_data_str($total_pendapatan + $rperbulan ),
		'pajak_09'	=> my_type_data_str($total_pendapatan + $rperbulan ),
		'pajak_10'	=> my_type_data_str($biaya_jabatan_baru ),
		'pajak_13'	=> my_type_data_str($biaya_jabatan_baru + $total_iuran ),
		'pajak_16'	=> my_type_data_str( $penghasilan_neto_baru * 12 ),
		'pajak_18'	=> my_type_data_str( $tarif_pajak_baru   ),
		'pajak_19'	=> my_type_data_str( $besar_pajak_baru   ),
		'pajak_21'	=> my_type_data_str( $pajak_baru  ), 
		);
	my_update_record( 'pr_yearly_income_pajak' , 'pr_yearly_income_id' , $idt, $cdatas);

	return round( $perbulan , -2 );
}


function get_total_pendapatan_sebelum_pph_yearly($karyawan_id , $kategori , $pr_yearly_income_id){
	$query = "SELECT nominal  FROM pr_yearly_income_karyawan a
	INNER JOIN pr_yearly_income_komponen b ON a.komponen_id = b.komponen_id
	WHERE ( b.komponen_pajak_1 = {$kategori} OR b.komponen_pajak_2 = {$kategori} ) 
	AND a.karyawan_id = {$karyawan_id} 
	AND a.pr_yearly_income_id = {$pr_yearly_income_id}   ";
	$result = my_query($query);
	$tx = array();
	while($row = my_fetch_array($result)){  
			$tx[]  =  ( $row['nominal'] < 0 ? (-1) *$row['nominal'] : $row['nominal'] );
	}
	
	$tk = array_sum($tx);  
	return round($tk);
}


function get_biaya_jabatan_yearly($total_pendapatan){
	$hasil = 0.05 * $total_pendapatan;
	if($hasil > 500000)	return 500000;
	return round($hasil);
}

function get_ptkp_karyawan_yearly($ptkp_karyawan_status , $tanggungan	){
	$query = "SELECT `value` FROM pr_konfigurasi_pajak WHERE `kolom_id` = '16' ";
	$result = my_query($query);
	$row = my_fetch_array($result);
	$nilai_tk = $row['value'];
	if( $ptkp_karyawan_status == 'TK' ){
		return $row['value'];
	}
	
	//CHECK KAWIN
	$query_kawin = "SELECT `value` FROM pr_konfigurasi_pajak WHERE `kolom_id` = '17' ";
	$result_kawin = my_query($query_kawin);
	$row_kawin = my_fetch_array($result_kawin);
	$nilai_kawin = $row_kawin['value'] + $nilai_tk;
	
	if($tanggungan == 0){
		return $nilai_kawin;
	}
	if($tanggungan > 3){
		$tanggungan = 3;
	}
	//CHECK TANGGUNGAN
	$query_tggn = "SELECT `value` FROM pr_konfigurasi_pajak WHERE `kolom_id` = '18' ";
	$result_tggn = my_query($query_tggn);
	$row_tggn = my_fetch_array($result_tggn); 
	return $nilai_kawin + ($tanggungan * $row_tggn['value']);
	
}

function tarif_progressif_yearly($total_pendapatan_yang_dipajak){
	$nilai = (int) $total_pendapatan_yang_dipajak;
	if($nilai <= 50000000){
		//$potongan = 0.05 * $nilai;
		$potongan = 5/95 * $nilai;
		
	}

	elseif($nilai <= 250000000){
		//$potongan = 0.15   * ( $nilai - 50000000 ) + 2500000;
		$potongan = 15/85  * ( $nilai - 50000000 ) + 2500000;
		
	}

	elseif($nilai <= 500000000){
		//$potongan = 0.25  * ( $nilai - 250000000 ) + 30000000 + 2500000;
		$potongan = 25/75  * ( $nilai - 250000000 ) + 30000000 + 2500000;
		
	}else{
		//$potongan = 0.30  * ( $nilai - 500000000 ) + 62500000 + 30000000 + 2500000;
		$potongan = 30/70  * ( $nilai - 500000000 ) + 62500000 + 30000000 + 2500000;
		
	}
	return $potongan;
}


function pajak_excel_report($pr_yearly_income_id){
	my_component_load('xl_builder' , false);
	$header = array() ;  
	$header['NIK']= array('style'=>'text-align:left;border-bottom:2px solid;width:8%');
	$header['Nama Karyawan']= array('style'=>'text-align:left;border-bottom:2px solid;width:22%');	
	$header['PTKP']= array('style'=>'text-align:left;border-bottom:2px solid;width:22%');
	$querysf= "SELECT * FROM pr_komponen_pajak
			ORDER BY komponen_pajak_id"
	;	
	$resultfs = my_query($querysf);
	while( $rowfs = my_fetch_array($resultfs) ){
		$header[$rowfs['label_pajak']]= array('style'=>'text-align:left;border-bottom:2px solid;width:'.strlen($rowfs['label_pajak']).'%');
	}
 
	 
	$query = "			
	SELECT * FROM karyawan b
	INNER JOIN pr_yearly_income_karyawan a ON a.karyawan_id=b.karyawan_id 
	WHERE a.pr_yearly_income_id= {$pr_yearly_income_id} 
	GROUP BY b.karyawan_id
	ORDER BY b.nik 
		"; 
	$result = my_query($query );
	$row = array();
	while( $ey = my_fetch_array($result) ){
		$karyawan = loaddata_karyawan($ey['karyawan_id']);
		$nilaipajak = nilai_nilai_pajak( $pr_yearly_income_id , $ey['karyawan_id'] ); 
		$det = array();
		$det['nik'] = "'".$karyawan['nik']; 
		$det['nama'] = $karyawan['nama_gelar'];
		$det['ptkp'] = $karyawan['ptkp_status']. ($karyawan['ptkp_status'] == 'TK' ?   ''  : ($karyawan['ptkp_tanggung'] > 3 ? 3 : $karyawan['ptkp_tanggung'] )  );
		for($i = 1; $i<=23; $i++){ 
			$det[$i] = $nilaipajak['pajak_'.sprintf('%02d' ,$i)];  
		} 
		$row[]=$det;  
	}
	$datas = table_rows_excel($row); 
	return table_builder_excel($header , $datas ,  24 ,false );
}

function nilai_nilai_pajak( $pr_yearly_income_id , $karyawan_id ){
	
	$querypajak = " SELECT * FROM pr_yearly_income_pajak 
		WHERE pr_yearly_income_id =".$pr_yearly_income_id." 
		AND karyawan_id=".$karyawan_id
	; 
	$result = my_query($querypajak);	
	return  my_fetch_array($result);	
}